<?php
require_once $ubi['SERVER']['SERVER_ROOT'] . '/dao/dbConnection.php';
require_once $ubi['SERVER']['SERVER_ROOT'] . '/model/user.php';

class UserDao {
	private $_db;	
	
	public function __construct() {
		$this->_db = new DbConnection();
		$this->_db->getConnection();
	}
	
	/**
	 *
	 * Add a user to the database
	 * @param $user: an instance of UserModel object
	 */
	public function addUser($user) {		
     	$query = sprintf("CALL sp_AddUser('%s', '%s', '%s', '%s', '%s', '%b', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%s', @UserId)",
     		$user->getUserName(), 
     		$user->getPasswordHash(), 
     		$user->getFirstName(),
     		$user->getLastName(), 
     		$user->getDateOfBirth(), 
     		$user->getGender(),
     		$user->getEmail(), 
     		$user->getCellphone(), 
     		$user->getTelephone(), 
     		$user->getOccupationID(),
     		$user->getAddressID(), 
     		$user->getBlast(), 
     		$user->getRoleID(), 
     		$user->getActivationCode());

     	$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return 0;
		}
     	
		$result = $this->_db->executeQuery('SELECT @UserId;');
		
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return 0;
		}
		
		if ($row = mysql_fetch_assoc($result)) {			
			$id = $row['@UserId'];
		}		
						
		mysql_free_result($result);
		
		return $id;
	}
	
	//this function used to activate user with the code passed in
	//return user id if activate successfully
	//return 0 if activate unsuccessfully
	//return -1 if user already activated
	public function activateUser($code) {
		//prepare the query
		$query = sprintf("SELECT UserId, Status, ActivationCode FROM users WHERE ActivationCode = '%s'", $code);
		
		//execute the query
		$result = $this->_db->executeQuery($query);		
		
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return;
		}
		
		$row = mysql_fetch_row($result);
		$userid = $row[0];
		$status = $row[1];
			
		if ($status == 1) { //if user already activated
			$this->_db->close();
			return -1;
		} else {
			//update the status of the user to 1
			$query = "UPDATE users SET Status = 1 WHERE UserId = " . $userid;
			$result = $this->_db->executeQuery($query);
				
			if (!$result) {
				return 0;				
			}			
		}
		
		return $userid;
	}

	/**
	 *
	 * Check if this username already exists in the db
	 * @param $username user name string
	 */
	public function isExistedByUserName($userName){
		//create query string to get all the user name
		$query = sprintf("SELECT UserId FROM users where UserName = '%s'", $userName);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return;
		}
		//get the number of results		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}
	}

	/**
	 *
	 * Check if this email already exists in the db
	 * @param $eamil email string
	 */
	public function isExistedByEmail($email){
		//create query string to get all the user name
		$query = sprintf("SELECT * FROM users where Email = '%s'", $email);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return;
		}
		//get the number of results		
		$num_rows = mysql_num_rows($result);
					
		return ($num_rows != 0);		
	}
	
	/**
	 *
	 * Check if this username already exists in the db
	 * @param $username user ID
	 */
	public function isExistedByUserId($userId){
		//create query string to get all the user name
		$query = sprintf("SELECT * FROM users where UserId = '%u'", $userId);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return;
		}		
		
		$num_rows = mysql_num_rows($result);

		mysql_free_result($result);
		
		return ($num_rows != 0);		
	}

	/**
	 *
	 * Check if this email already exists in the db
	 * @param $email user name string
	 */
	public function isEmailExisted($email){
		//create query string to get all the user name
		$query = sprintf("SELECT * FROM users where Email = '%s'", $email);
		
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . mysql_error();
		    return;
		}
		//get the number of results		
		$num_rows = mysql_num_rows($result);
		mysql_free_result($result);					
		$this->_db->close();
		return ($num_rows != 0);
	}
	
	/**
	 * 
	 * Get the user with the userid passed in
	 * @param unknown_type $userid
	 */
	public function getUser($userID) {
		//create query string to get user
		$query = sprintf("SELECT * FROM users WHERE UserId = '%u'", $userID);
		
		//query to get user
		$result = $this->_db->executeQuery($query);		
		
		//check the result
		if ($row = mysql_fetch_assoc($result)) {			
			$user = $this->setUserByAssoc($row);
			$query = sprintf("SELECT PrivacyId FROM privacy_email WHERE UserId = '%u'", $userID);
			$resultPrivacy = $this->_db->executeQuery($query);
			$privacies = array(); 
			while($row = mysql_fetch_assoc($resultPrivacy)) {
				$privacies[] = $row['PrivacyId'];
			}			
			$user->setEmailPrivacies($privacies);
			mysql_free_result($resultPrivacy);	
		}		
		mysql_free_result($result);
		return $user;
	}
	
	/**
	 * 
	 * This function try to login
	 * return null if fail to login
	 * return the user record if login successfully
	 */
	public function login($username, $password) {
		//create query to check
		
		$query = sprintf("SELECT * FROM users WHERE UserName = '%s' AND PasswordHash = md5('%s')", 
				$username, $password);

		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;
		}
		
		//check the result
		if ($row = mysql_fetch_assoc($result)) {
			$user = $this->setUserByAssoc($row);

			$query = sprintf("CALL sp_Login('%d')", $user->getUserID());
			$this->_db->executeQuery($query);
		}
		
		mysql_free_result($result);
		
		return $user;
	}
	
	public function updateBlast($content, $userid) {
		$query = sprintf("UPDATE users SET Blast = CONVERT('%s' USING UTF8), BlastUpdate = NOW() WHERE UserId = '%d'", 
			$content, $userid);
		
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return 0;		 
		}		

		return 1;
	}
	
	public function getUserListByUserIdList($idList) {		
		//create query string to get user
		$query = 'SELECT * FROM users WHERE UserId IN (' . $idList[0];
		
		if (count($idList) > 0) {
			for ($i = 1; $i < count($idList); $i++) {
				$query .= ', ' . $idList[$i];
			}
		} else {
			$query .= '0';
		}
		
		$query .= ')';
		
		//query to get users
		$result = $this->_db->executeQuery($query);				
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();		 
		}
		
		$users = array();
		while ($row = mysql_fetch_assoc($result)) {			
			$user = $this->setUserByAssoc($row);			
			$users[$user->getUserID()] = $user;
		}
		
		mysql_free_result($result);
		
		return $users;
	}
	
	public function getUserByUserName($userName) {
		//create query string to get user
		$query = sprintf("SELECT * FROM users WHERE UserName = '%s'", mysql_real_escape_string($userName));
		
		//query to get user
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error(); 
		}		
		
		//check the result
		if ($row = mysql_fetch_assoc($result)) {					
			$user = $this->setUserByAssoc($row);
		}
		
		mysql_free_result($result);
		
		return $user;
	}
	
	public function getUserByToken($token) {
		$user = null;
		$query = sprintf("SELECT * FROM users WHERE Token = '%s'", mysql_real_escape_string($token));
		
		//query to get user
		$result = $this->_db->executeQuery($query);		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();		 
		}		
		
		//check the result
		if ($row = mysql_fetch_assoc($result)) {
			$user = $this->setUserByAssoc($row);
		}
		
		mysql_free_result($result);
		
		return $user;
	}
	
	public function getUserByEmail($email) {
		$user = null;
		$query = sprintf("SELECT * FROM users WHERE Email = '%s'", mysql_real_escape_string($email));

		//query to get user
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();		 
		}

		//check the result
		if ($row = mysql_fetch_assoc($result)) {
			$user = $this->setUserByAssoc($row);
		}
		
		mysql_free_result($result);
		
		return $user;
	}
	
	public function updateToken($user) {
		$query = sprintf("UPDATE users SET Token = '%s' WHERE UserId = '%s'", $user->getToken(), $user->getUserID());
		
		$result = $this->_db->executeQuery($query);
		if (!$result)
		{
			echo 'Could not run upadte token due to ' . mysql_error();
			return 0;
		}
		
		return 1;
	}
	
	public function updatePassword($user) {
		$query = sprintf("UPDATE users SET PasswordHash = '%s' WHERE UserId = '%s'", $user->getPasswordHash(), $user->getUserID());
		
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run upadte token due to ' . mysql_error();
			return 0;
		}
		
		return 1;
	}
	
	public function getBestUsers() {
		global $ubi;
		$query = sprintf("SELECT * FROM users ORDER BY Points DESC LIMIT %d", $ubi['SITE']['NUMBER_BEST_USER']);
		$result = $this->_db->executeQuery($query);
		
		$users = array();
		while ($row = mysql_fetch_assoc($result)) {			
			$users[] = $this->setUserByAssoc($row);
		}

		return $users;
	}
	
	public function getNumberOfUser() {
		$query = "SELECT COUNT(*) FROM users";
					
		$result = $this->_db->executeQuery($query);

		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}		
	}	
	
	public function addScore($userID, $score) {
		$query = sprintf("UPDATE users SET Points = Points + %d WHERE UserId = '%d'", $score, $userID);
		$result = $this->_db->executeQuery($query);

		return;	
	}
	
	public function updateUser($user) {
		$query = sprintf("UPDATE users 
						SET Email = '%s', 
						LastName = CONVERT('%s' using UTF8), 
						FirstName = CONVERT('%s' using UTF8), 
						Gender = %d, 
						DateOfBirth = '%s', 
						OccupationId = %d,
						Telephone = '%s',
						Cellphone = '%s',
						CoupleUserName = '%s',
						CoupleNickName = '%s',
						MariageStatus = '%s' 
						WHERE UserId = '%s'", 
						$user->getEmail(), 
						$user->getLastName(), 
						$user->getFirstName(), 
						$user->getGender(), 
						$user->getDateOfBirth(), 
						$user->getOccupationID(),
						$user->getTelephone(),
						$user->getCellphone(),
						$user->getCoupleUserName(),
						$user->getCoupleNickName(),
						$user->getMariageStatus(),
						$user->getUserID());
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run upadte token due to ' . mysql_error();
			return 1;
		}
		
		return 0;
	}
	
	public function updateLastLoginAndIPAddress($userID, $ipAddress) {
		if (isset($ipAddress)) {
			$query = sprintf("UPDATE users SET LastLogin = '%d', IpAddress = '%s' WHERE UserId = '%d'", $_SERVER['REQUEST_TIME'], $ipAddress, $userID);
		} else {
			$query = sprintf("UPDATE users SET LastLogin = '%d' WHERE UserId = '%d'", $_SERVER['REQUEST_TIME'], $userID);
		}
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run upadte token due to ' . mysql_error();
		}
	}
	
	public function getUsersFromTo($from, $to) {
		$query = sprintf("SELECT * FROM users LIMIT %u, %u", $from, $to);
							
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		$userList = array();
		while($row = mysql_fetch_assoc($result)) {			
			$userList[] = $this->setUserByAssoc($row);
		}
		
		return $userList;
	}
	
	private function setUserByAssoc($row) {
		$user = new UserModel();
			
		$user->setUserID($row['UserId']);
		$user->setUserName($row['UserName']);
		$user->setPoints($row['Points']);
		$user->setTotalRate($row['TotalRate']);
		$user->setPositiveRate($row['PositiveRate']);
		$user->setNegativeRate($row['NegativeRate']);
		$user->setPosComRate($row['PosComRate']);
		$user->setNegComRate($row['NegComRate']);
		$user->setFirstName($row['FirstName']);
		$user->setLastName($row['LastName']);
		$user->setDateOfBirth($row['DateOfBirth']);
		$user->setGender($row['Gender']);
		$user->setEmail($row['Email']);
		$user->setCellPhone($row['Cellphone']);
		$user->setTelephone($row['Telephone']);
		$user->setOccupationID($row['OccupationId']);
		$user->setAddressID($row['AddressId']);
		$user->setFrequency($row['Frequency']);
		$user->setBlast($row['Blast']);
		$user->setRoleID($row['RoleId']);
		$user->setActivationCode($row['ActivationCode']);
		$user->setToken($row['Token']);
		$user->setIpAddress($row['IpAddress']);
		$user->setFrequency($row['Frequency']);
		$user->setStatus($row['Status']);
		$user->setCreatedDate($row['CreatedDate']);
		$user->setLastLogin($row['LastLogin']);
		$user->setLastUpdate($row['LastUpdate']);
		$user->setBlastUpdate($row['BlastUpdate']);
		$user->setCoupleUserName($row['CoupleUserName']);
		$user->setCoupleNickName($row['CoupleNickName']);
		$user->setMariageStatus($row['MariageStatus']);
		
		return $user;
	}
}
?>